#include "stdafx.h"
#include "QSqlQueryEx.h"
#include <QDebug>

QSqlQueryEx::QSqlQueryEx(QSqlDatabase db)
	:QSqlQuery(db),m_db(db)
{ 
	if(!m_db.isValid())
	{
		Q_ASSERT(0);
	}

	m_lstWhereSQL.clear();
	m_nLimit = -1;
	m_nOffset = -1;
	m_strWhere = " and ";
}

QSqlQueryEx::~QSqlQueryEx()
{

}

void QSqlQueryEx::where(QString strFiledName,QVariant value)
{
	QString strSQL = QString(" %1 = :%2 ").arg(strFiledName).arg(strFiledName);

	m_lstWhereSQL.append(strSQL); 

	m_fv[strFiledName] = value;

	m_strWhere = " and ";
}

void QSqlQueryEx::or_where( QString strFiledName,QVariant value )
{
	where(strFiledName,value);

	m_strWhere = " or ";
}

bool QSqlQueryEx::get(QString strTableName)
{
	m_strTableName = strTableName;

	return get();
}

bool QSqlQueryEx::get(QString strTableName,int nLimit,int nOffset)
{
	m_strTableName = strTableName;
	m_nLimit = nLimit;
	m_nOffset = nOffset;
	
	return get();
}

bool QSqlQueryEx::get()
{
	if(m_strTableName.isEmpty()) {Q_ASSERT(0);}

	QString strSelect = doSelect();
	QString strWhere = doWhere();
	QString strLimit = doLimit();

	QString strSQL = strSelect + " from " +m_strTableName + strWhere + 	strLimit;
	
	qDebug()<<strSQL;

	if(!prepare(strSQL))  return false;
	
	if(!bindWhereValue()) return false;

	if(!exec())	return false;
	
	if(!next())	return false;

	return true;
}
	

QString QSqlQueryEx::doWhere()
{
	QString strWhere;
	if(m_lstWhereSQL.isEmpty())  return QString();
	for (int i=0;i<m_lstWhereSQL.size();i++)
	{
		if(i != m_lstWhereSQL.size() - 1)
			strWhere += m_lstWhereSQL.at(i) + m_strWhere;
		else
			strWhere += m_lstWhereSQL.at(i);
	}

	strWhere = " where " + strWhere;

	qDebug() <<strWhere;

	return strWhere;
}

void QSqlQueryEx::from( QString strTableName )
{
	if(strTableName.isEmpty()) {Q_ASSERT(0);}
	m_strTableName = " " + strTableName + " ";
}

QString QSqlQueryEx::doSelect()
{
	QString strSelect;

	if(m_strSelFiledName.isEmpty())
		strSelect = " select * ";
	else
		strSelect = " select " + m_strSelFiledName;

	return strSelect;
}

void QSqlQueryEx::select( QString strFiledName )
{
	m_strSelFiledName = " " +strFiledName + " ";
}

QString QSqlQueryEx::doLimit()
{
	if(m_nLimit >= 0 && m_nOffset >= 0)
		return QString(" limit %1 offset %2 ").arg(m_nLimit).arg(m_nOffset);
	else
		return QString();
}

bool QSqlQueryEx::bindWhereValue()
{
	QList<QString>  lstKey = m_fv.keys();
	for (int i=0;i<lstKey.size();++i)
	{
		QString key = ":" + lstKey.at(i);
		QVariant value = m_fv.value(lstKey.at(i));
		if(!value.isValid()) {Q_ASSERT(0); return false;}
		bindValue(key,value);
	}
	return true;
}

QString QSqlQueryEx::doFrom()
{
	return " from " + m_strTableName;
}

bool QSqlQueryEx::insert( QString strTableName,FILEDVALUE data )
{
	if(data.size() <= 0) return false;

	from(strTableName);

	QList<QString> lstKey = data.keys();
	QString strKeys;
	QString strKValue;
	for (int i=0;i<lstKey.size();++i)
	{
		if(i != lstKey.size() - 1)
		{
			strKeys += lstKey.at(i) + ",";
			strKValue += ":"+lstKey.at(i) + ",";
		}
		else
		{
			strKeys += lstKey.at(i);
			strKValue += ":"+lstKey.at(i);
		}
	}

	QString strSQL = " insert into " + strTableName + " ( "  + strKeys + " ) values (" + strKValue + " ) ";

	qDebug() << strSQL;

	if(!prepare(strSQL))  return false;
	for(int i=0;i<lstKey.size();++i)
	{
		QString strKey = ":"+lstKey.at(i);
		QVariant value = data.value(lstKey.at(i));
		bindValue(strKey,value);
	}

	return exec();
}

int QSqlQueryEx::insert_id( QString strFiledID )
{
	QString strSQL = QString("select max(%1) from %2").arg(strFiledID).arg(m_strTableName);
	if(!exec(strSQL)) return -1;
	if(!next())	return -1;

	return value(0).toInt();

}

bool QSqlQueryEx::update( QString strTableName,FILEDVALUE data )
{
	if(data.size() <= 0) return false;

	from(strTableName);

	QList<QString> lstKey = data.keys();
	QString strKeys;

	for (int i=0;i<lstKey.size();++i)
	{
		if(i != lstKey.size() - 1)
		{
			strKeys += lstKey.at(i) + " = :"+lstKey.at(i) + ",";
		}
		else
		{
			strKeys += lstKey.at(i) + " = :"+lstKey.at(i) ;

		}
	}

	QString strSQL = " update " + m_strTableName + " set " + strKeys + doWhere();

	qDebug() << strSQL;

	if(!prepare(strSQL))  return false;
	//bindvalue set;
	{
		for(int i=0;i<lstKey.size();++i)
		{
			QString strKey = ":"+lstKey.at(i);
			QVariant value = data.value(lstKey.at(i));
			bindValue(strKey,value);
		}
	}

	if(!bindWhereValue()) return false;

	return exec();
}

bool QSqlQueryEx::delete_data( QString strTableName )
{
	from(strTableName);

	QString strSQL = " delete from " + m_strTableName + doWhere(); 
	qDebug() << strSQL;

	if(!prepare(strSQL))  return false;

	if(!bindWhereValue()) return false;

	return exec();
}
